package com.theo.mapper;

import com.theo.entity.EpidemicDailyDetailInfo;
import com.theo.entity.EpidemicDetailInfo;
import com.theo.entity.EpidemicInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.theo.vo.CountryCharts;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author 方涛
 * @since 2021-02-02
 */
@Repository
public interface EpidemicInfoMapper extends BaseMapper<EpidemicInfo> {
    /***
     * 录入疫情数据
     * @param epidemicInfo
     * @return
     */
    int saveEpidemicInfo(EpidemicInfo epidemicInfo);

    @Select("SELECT e1.`city_id`,temp.`city_name`,e1.`date`,e1.`affirmed` affirmed_total,e1.`suspected` suspected_total,e1.`cured` cured_total,\n" +
            "e1.`dead` dead_total,e1.`overseas_input` overseas_input_total,e1.`symptomless` symptomless_total\n" +
            "FROM epidemic_db.epidemic_info e1 \n" +
            "RIGHT OUTER JOIN city_info temp \n" +
            "ON e1.city_id = temp.city_id\n" +
            "WHERE e1.date = #{date};")
    List<EpidemicDetailInfo> findLastestData(@Param("date") String date);

    @Select("SELECT e.city_id,temp.country_name city_name,e.affirmed,e.suspected,e.cured,e.dead\n" +
            "FROM epidemic_info e RIGHT OUTER JOIN country_info temp\n" +
            "ON e.city_id = temp.country_id\n" +
            "WHERE e.date=#{date}")
    List<EpidemicDetailInfo> findLastestGlobalData(@Param("date") String date);

    @Select("SELECT `date`, SUM(affirmed) affirmed_total,SUM(suspected) suspected_total,SUM(dead) dead_total,SUM(cured) cured_total,SUM(symptomless) symptomless_total,SUM(overseas_input) overseas_input_total\n" +
            "FROM epidemic_info WHERE `date`=#{date} AND LENGTH(`city_id`)=9 AND `city_id`!='100000000'")
    EpidemicDailyDetailInfo findData(@Param("date") String date);


    @Select("SELECT `date`, SUM(affirmed) affirmed_total,SUM(suspected) suspected_total,SUM(dead) dead_total,SUM(cured) cured_total,SUM(symptomless) symptomless_total,SUM(overseas_input) overseas_input_total\n" +
            "FROM epidemic_info WHERE `date`=#{date} AND LENGTH(`city_id`)!=9 AND `city_id`!='Global'")
    EpidemicDailyDetailInfo findGlobalData(@Param("date") String date);

    @Insert("INSERT INTO epidemic_info(`city_id`,`date`,`affirmed`,`suspected`,`dead`,`cured`,`symptomless`,`overseas_input`)\n" +
            "VALUES('100000000',#{date},#{affirmedTotal},#{suspectedTotal},#{deadTotal},#{curedTotal},#{symptomlessTotal},#{overseasInputTotal});")
    int insertData(EpidemicDailyDetailInfo epidemicDailyDetailInfo);

    @Insert("INSERT INTO epidemic_info(`city_id`,`date`,`affirmed`,`suspected`,`dead`,`cured`,`symptomless`,`overseas_input`)\n" +
            "VALUES('Global',#{date},#{affirmedTotal},#{suspectedTotal},#{deadTotal},#{curedTotal},#{symptomlessTotal},#{overseasInputTotal});")
    int insertGLobalData(EpidemicDailyDetailInfo epidemicDailyDetailInfo);

    int updateData(EpidemicDailyDetailInfo epidemicDailyDetailInfo);

    @Select("SELECT DISTINCT `date` FROM epidemic_info WHERE `date` NOT IN\n" +
            "(SELECT `date` FROM epidemic_info WHERE city_id='100000000')  ORDER BY `date`")
    List<Date> selectDateData();

    @Select("SELECT DISTINCT `date` FROM epidemic_info WHERE `date` NOT IN\n" +
            "(\n" +
            "SELECT `date` FROM epidemic_info WHERE city_id='Global'\n" +
            ") ORDER BY `date`")
    List<Date> selectDateGlobalData();

    @Select("SELECT `date`,`affirmed`,`suspected`,`dead`,`cured`,`symptomless`,`overseas_input` FROM epidemic_info where `city_id`='100000000' order by `date`")
    List<EpidemicInfo> selectDataForChart();

    @Select("SELECT `date`,`affirmed`,`suspected`,`dead`,`cured`,`symptomless`,`overseas_input` FROM epidemic_info where `city_id`='Global' order by `date`")
    List<EpidemicInfo> selectDataForGlobalChart();

    List<EpidemicInfo> selectDataForChart2();
    /***
     * 查询某天的数据
     * @param date
     * @return
     */
    @Select("SELECT c.`city_name`,e.`affirmed` affirmed_total,e.`suspected` suspected_total,e.`dead` dead_total,e.`cured` cured_total,e.`symptomless` symptomless_total,e.`overseas_input` overseas_input_total\n" +
            "FROM `city_info` c\n" +
            "LEFT JOIN `epidemic_info` e\n" +
            "ON c.`city_id`=e.`city_id`\n" +
            "WHERE e.`date` = #{date}")
    List<EpidemicDetailInfo> findAllData(@Param("date")String date);

    @Select("SELECT `epidemic_id`,`city_name`,`date`,`affirmed`,`suspected`,`dead`,`cured`,`symptomless`,`overseas_input` \n" +
            "FROM `epidemic_info` e INNER JOIN city_info c\n" +
            "ON e.`city_id`=c.`city_id`")
    List<EpidemicInfo> findAll();


    @Select("SELECT `epidemic_id`,`city_name`,`date`,`affirmed`,`suspected`,`dead`,`cured`,`symptomless`,`overseas_input` \n" +
            "FROM `epidemic_info` e INNER JOIN city_info c\n" +
            "ON e.`city_id`=c.`city_id`\n" +
            "WHERE `date`=#{date}")
    List<EpidemicInfo> findAllByDate(@Param("date") String date);

    @Select("select * from epidemic_info where epidemic_id=#{id}")
    EpidemicInfo queryEpidemicInfoById(@Param("id") Integer id);

    void deleteDataById(Integer id);

    void updateData(EpidemicInfo epidemicInfo);

}
